package org.tigr.htc.persist;

/*
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

import org.exolab.castor.persist.spi.Complex;
import org.tigr.antware.shared.util.Logger;
import org.tigr.htc.cmd.CmdState;
import org.tigr.htc.cmd.CommandStatus;
import org.tigr.htc.cmd.Command;
import org.tigr.htc.cmd.Param;
import org.tigr.htc.cmd.ParamType;
import org.tigr.htc.cmd.Task;
import org.tigr.htc.common.HTCConfig;

/**
 * The class <code>DirectDatabase</code> class is an implementation of the persist class
 * that connects directly to the database. Currently only the methods used for status pages
 * are supported by this class. This class implements a singleton pattern so it is shared
 * by all the classes in the JVM.
 *
 */

public class DirectDatabase implements IPersist {
    /**
     * The variable <code>logger</code> holds the instance of the logger for this class.
     */
    private static Logger logger = new Logger("org.tigr.htc.persist.DirectDatabase");
    private static DirectDatabase instance;
    private String url;
    private String userName;
    private String password;
    private String className;
    private String connectString;
    
    /**
     * The constructor <code>DirectDatabase</code> creates a new instance of this class.
     * 
     */
    private DirectDatabase() {
        
        url = HTCConfig.getProperty("db.url");
        password = HTCConfig.getProperty("db.pass");
        userName = HTCConfig.getProperty("db.user");
        className = HTCConfig.getProperty("db.class.name");

        if (logger.isInfoEnabled()) {
            logger.info("Creating database persist layer using URL: " + url + " user name: " + userName);
        }

        try {
            // Load the driver to make sure that a connection can be established
            Class.forName(className).newInstance();
        }
        catch (ClassNotFoundException e) {
            logger.fatal("Could not find class to connect to database: " + className);
        }
        catch (InstantiationException e) {
            logger.fatal("Could not instantiate class to connect to database: " + className);
        }
        catch (IllegalAccessException e) {
            logger.fatal("Could not load class to connect to database: " + className);
        }
        logger.info("Finished loading driver class.");
        connectString = url + "?user=" + userName + "&password=" + password;
    }

    /**
     * The method <code>getConnection</code> returns a database connection that can be used
     * for database queries.
     * 
     * @return a <code>Connection</code> object that can be used to execute queries
     * 
     * @throws SQLException a <code></code>
     */
    private Connection getConnection () throws SQLException {
        if (logger.isDebugEnabled()) {
            logger.debug("Trying to establish a database connection.");
        }
        Connection connection = null;
        connection = DriverManager.getConnection(connectString);
        return connection;
    }
    
    /**
     * The method <code>getInstance</code> returns the instance of the persist layer.
     * 
     * @return a <code>IPersist</code> instance that will conenct directly to the database
     */
    public static IPersist getInstance () {
        if (instance == null) {
            logger.info("Creating a new instance of the persist layer.");
            instance = new DirectDatabase();
        }
        return instance;
    }

    /* (non-Javadoc)
     * @see org.tigr.htc.persist.IPersist#getNewID()
     */
    public long getNewID() {
        logger.warn("This method is not yet implemented.");
        return 0;
    }

    /* (non-Javadoc)
     * @see org.tigr.htc.persist.IPersist#create(java.lang.Object)
     */
    public void create(Object p_cmd) {
        logger.warn("This method is not yet implemented");
    }

    /* (non-Javadoc)
     * @see org.tigr.htc.persist.IPersist#update(java.lang.Object)
     */
    public void update(Object p_cmd) {
        logger.warn("This method is not yet implemented");        
    }

    /* (non-Javadoc)
     * @see org.tigr.htc.persist.IPersist#delete(long, java.lang.Class)
     */
    public void delete(long p_id, Class p_class) {
        logger.warn("This method is not yet implemented");        
    }

    /* (non-Javadoc)
     * @see org.tigr.htc.persist.IPersist#retrieveTasks(long)
     */
    public Vector retrieveTasks(long p_id) {
        if (logger.isDebugEnabled()) {
            logger.debug("Attempting to locate command with ID " + p_id);
        }
        Vector tasks = new Vector();
        Command cmd = null;
        StringBuffer sqlString = new StringBuffer(
                "select id, command_id, machine, state, tindex, worker, " +
                "return_value, message from Task where command_id = " + p_id +
                " order by tindex");
        // execute the query and build the Command objects

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = instance.getConnection();
            stmt = conn.createStatement();
            if (logger.isDebugEnabled()) {
                logger.debug("Executing SQL: " + sqlString);
            }
            rs = stmt.executeQuery(sqlString.toString());

            // If there is a record build the Command
            while (rs.next()) {
                Task task = new Task();
                task.setCommand(rs.getLong("command_id"));
                task.setMachine(rs.getString("machine"));
                task.setMessage(rs.getString("message"));
                task.setState(rs.getString("state"));
                task.setIndex(rs.getInt("tindex"));
                task.setWorker(rs.getInt("worker"));
                task.setReturnValue(rs.getInt("return_value"));
                tasks.add(task);
            }
        }
        catch (SQLException e) {
            logger.warn("Could not read tasks.", e);
        }
        finally {
            // it is a good idea to release
            // resources in a finally{} block
            // in reverse-order of their creation
            // if they are no-longer needed

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) { 
                    // ignore 
                }

                rs = null;
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { // ignore 
                
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlEx) { // ignore 
                
                }

                conn = null;
            }
            
        }
        if (logger.isDebugEnabled()) {
            logger.debug("Returning tasks: " + tasks);
        }
        return tasks;
    }

    /* (non-Javadoc)
     * @see org.tigr.htc.persist.IPersist#find(long, java.lang.Class)
     */
    public Object find(long p_id, Class p_class) {
        if (logger.isDebugEnabled()) {
            logger.debug("Attempting to locate command with ID " + p_id);
        }
        Command cmd = null;
        StringBuffer sqlString = new StringBuffer(
                "select id, grid_id, ctype, username, cgroup, " +
                "state, retry_count, times, max_workers, num_tasks, name, queue," +
                "email, command, submitURL, initial_dir, output, input, " +
                "error, notify_script, request_time, submit_time, start_time, " +
                "end_time, monitor_host from Command where id = " + p_id);
        // execute the query and build the Command objects

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List commands = new Vector();
        try {
            conn = instance.getConnection();
            stmt = conn.createStatement();
            if (logger.isDebugEnabled()) {
                logger.debug("Executing SQL: " + sqlString);
            }
            rs = stmt.executeQuery(sqlString.toString());

            // If there is a record build the command
            if (rs.next()) {
                cmd = new Command();
                cmd.setID(rs.getLong("id"));
                cmd.setRetryCount(rs.getInt("retry_count"));
                cmd.setTimes(rs.getInt("times"));
                cmd.setNumTasks(rs.getInt("num_tasks"));
                cmd.setMaxWorkers(rs.getInt("max_workers"));
                cmd.setGridJobID(rs.getString("grid_id"));
                cmd.setType(rs.getString("ctype"));
                cmd.setUserName(rs.getString("username"));
                cmd.setGroup(rs.getString("cgroup"));
                cmd.setState(rs.getString("state"));
                cmd.setName(rs.getString("name"));
                cmd.setQueue(rs.getString("queue"));
                cmd.setGroup(rs.getString("cgroup"));
                cmd.setEmail(rs.getString("email"));
                cmd.setCommand(rs.getString("command"));
                cmd.setSubmitURL(rs.getString("submitURL"));
                cmd.setInitialDir(rs.getString("initial_dir"));
                cmd.setOutput(rs.getString("output"));                
                cmd.setInput(rs.getString("input"));
                cmd.setError(rs.getString("error"));
                cmd.setNotifyScript(rs.getString("notify_script"));
                cmd.setMonitorHost(rs.getString("monitor_host"));
                cmd.setSubmitTime(rs.getLong("submit_time"));
                cmd.setRequestTime(rs.getLong("request_time"));
                cmd.setStartTime(rs.getLong("start_time"));
                cmd.setEndTime(rs.getLong("end_time"));
            }

            // If a Command is found by the earlier execute query, then 
            // Set Status and Param values of the Command
            if (cmd != null) {
				// After the command is read, read the status for this command
				sqlString.setLength(0);
				sqlString
						.append("select message, errors, running, completed, waiting, "
								+ " return_value from CommandStatus where command_id = "
								+ p_id);

				if (logger.isDebugEnabled()) {
					logger.debug("Executing SQL: " + sqlString);
				}
				rs = stmt.executeQuery(sqlString.toString());
				if (rs.next()) {
					CommandStatus cs = new CommandStatus();
					cs.setMessage(rs.getString("message"));
					cs.setErrors(rs.getInt("errors"));
					cs.setRunning(rs.getInt("running"));
					cs.setCompleted(rs.getInt("completed"));
					cs.setWaiting(rs.getInt("waiting"));
					cs.setReturnValue(rs.getInt("return_value"));
					cmd.setStatus(cs);
				}

				// After the status is read, read the param list for this Command
				sqlString.setLength(0);
				sqlString.append("select ptype, pkey, pvalue "
						+ " from Param where command_id = " + p_id);

				if (logger.isDebugEnabled()) {
					logger.debug("Executing SQL: " + sqlString);
				}
				rs = stmt.executeQuery(sqlString.toString());
				Vector params = new Vector();
				while (rs.next()) {
					Param param = new Param(rs.getString("pkey"), rs
							.getString("pvalue"), ParamType.getType(rs
							.getString("ptype")));
					params.add(param);
				}
				cmd.setParams(params);
			}
        }
        catch (SQLException e) {
            logger.warn("Could not read commands.", e);
        }
        finally {
            // it is a good idea to release
            // resources in a finally{} block
            // in reverse-order of their creation
            // if they are no-longer needed

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) { 
                    // ignore 
                }

                rs = null;
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { // ignore 
                
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlEx) { // ignore 
                
                }

                conn = null;
            }
            
        }
        if (logger.isDebugEnabled()) {
            if (cmd != null) logger.debug("Returning Command: " + cmd.getID());
        }
        return cmd;
    }

    /* (non-Javadoc)
     * @see org.tigr.htc.persist.IPersist#find(org.exolab.castor.persist.spi.Complex, java.lang.Class)
     */
    public Object find(Complex p_id, Class p_class) {
        logger.warn("This method is not yet implemented");
        return null;
    }

    /* (non-Javadoc)
     * @see org.tigr.htc.persist.IPersist#searchCommands(java.lang.String, java.lang.String, java.lang.String)
     */
    public List searchCommands(String p_where, String p_order, String p_limit) {
        if (logger.isDebugEnabled()) {
            logger.debug("Attempting to find commands with where " + p_where +
                    " order " + p_order);
        }
        StringBuffer sqlString = new StringBuffer(
                "select id, grid_id, ctype, username, cgroup, " +
                "state, retry_count, times, max_workers, num_tasks, name, queue," +
                "email, command, submitURL, initial_dir, output, input, " +
                "error, notify_script, request_time, submit_time, start_time, " +
                "end_time, monitor_host from Command ");
        if ((p_where != null) && (p_where.length() > 0)) {
            // Replace 'type' with 'ctype'
            p_where = p_where.replaceAll("type", "ctype");
            
            // Replace 'gridJobID' with 'grid_id'
            // Make sure that you replace this before replacing ID otherwise
            // things could break
            p_where = p_where.replaceAll("gridJobID", "grid_id");

            // Replace 'ID' with 'id'
            p_where = p_where.replaceAll("ID", "id");
            
            // Replace 'requestTime' with 'request_time'
            p_where = p_where.replaceAll("requestTime", "request_time");
            
            // replace all userName with username which is the actual field
            p_where = p_where.replaceAll("userName", "username");
            
            // If a where string has been specified then append the where string
            sqlString.append("where ").append(p_where);
        }

        if ((p_order != null) && (p_order.length() > 0)) {
            // replace all userName with username which is the actual field
            p_order = p_order.replaceAll("userName", "username");
            // If a order string has been specified then append the order by clause
            sqlString.append(" order by ").append(p_order);
        }
        
        // execute the query and build the Command objects

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List commands = new Vector();
        try {
            conn = instance.getConnection();
            stmt = conn.createStatement();
            if (logger.isDebugEnabled()) {
                logger.debug("Executing SQL: " + sqlString);
            }
            rs = stmt.executeQuery(sqlString.toString());

            // While there are records build the Commands
            while (rs.next()) {
                Command cmd = new Command();
                cmd.setID(rs.getLong("id"));
                cmd.setRetryCount(rs.getInt("retry_count"));
                cmd.setTimes(rs.getInt("times"));
                cmd.setNumTasks(rs.getInt("num_tasks"));
                cmd.setMaxWorkers(rs.getInt("max_workers"));
                cmd.setGridJobID(rs.getString("grid_id"));
                cmd.setType(rs.getString("ctype"));
                cmd.setUserName(rs.getString("username"));
                cmd.setGroup(rs.getString("cgroup"));
                cmd.setState(rs.getString("state"));
                cmd.setName(rs.getString("name"));
                cmd.setQueue(rs.getString("queue"));
                cmd.setGroup(rs.getString("cgroup"));
                cmd.setEmail(rs.getString("email"));
                cmd.setCommand(rs.getString("command"));
                cmd.setSubmitURL(rs.getString("submitURL"));
                cmd.setInitialDir(rs.getString("initial_dir"));
                cmd.setOutput(rs.getString("output"));                
                cmd.setInput(rs.getString("input"));
                cmd.setError(rs.getString("error"));
                cmd.setNotifyScript(rs.getString("notify_script"));
                cmd.setMonitorHost(rs.getString("monitor_host"));
                cmd.setSubmitTime(rs.getLong("submit_time"));
                cmd.setRequestTime(rs.getLong("request_time"));
                cmd.setStartTime(rs.getLong("start_time"));
                cmd.setEndTime(rs.getLong("end_time"));
                
                commands.add(cmd);
            }
        }
        catch (SQLException e) {
            logger.warn("Could not read Commands.", e);
        }
        finally {
            // it is a good idea to release
            // resources in a finally{} block
            // in reverse-order of their creation
            // if they are no-longer needed

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) { 
                    // ignore 
                }

                rs = null;
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { // ignore 
                
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlEx) { // ignore 
                
                }

                conn = null;
            }
            
        }
        
        if (logger.isDebugEnabled()) {
            logger.debug("Returning Command count: " + commands);
        }
        return commands;
    }

    /* (non-Javadoc)
     * @see org.tigr.htc.persist.IPersist#countCommands(java.lang.String)
     */
    public Integer countCommands(String pWhere) {
        return null;
    }
    
    public static void main(String [] args) throws SQLException {
        Logger.initializeLogging();
        DirectDatabase db = (DirectDatabase) DirectDatabase.getInstance();
        Connection conn = null;
        String where = "state = \"" + CmdState.WAITING + "\" OR state = \"" + CmdState.RUNNING
                + "\" OR state = \"" + CmdState.INIT + "\"";
        String order = "userName";
        List commands = db.searchCommands(where, order, null);
    }
}
